OpenEdge Development:<br />Programming Interfaces Writing queries using the CONTAINS operator

Release 10.1A: OpenEdge Development:
Programming Interfaces

Writing queries using the CONTAINS operator

Once you associate a compiled word-break table with a database that has word indexes, if necessary, populate the database and rebuild the word indexes. You can then write queries that use the CONTAINS operator.

Syntax of the CONTAINS operator of the WHERE option

The CONTAINS operator has the following syntax in the WHERE option of the record phrase:
Syntax
WHERE field CONTAINS string-expression 
field
A field or array of type CHARACTER that participates in a word index.
string-expression
An expression of type CHARACTER that represents possible contents of field.

The syntax of string–expression is as follows:
Syntax
"word [ [ & | | | ! | ^ ] word ] ..." 
word
The word to search for.

The ampersand (&) represents logical AND, while the vertical bar (|), the exclamation point (!), and the caret (^) represent logical OR. AND limits your search to records that contain all words you specify, while OR enlarges your search to include any word you specify. You can combine ANDs and ORs within string–expression. You can also group items with parentheses to create complex search conditions.

You can use a wild card on the end of a string. For example, the string “sales*” represents “sales,” “saleswoman,” “salesman,” “salesperson,” and similar strings.

You can also define a character variable and assign a value to that variable.

Examples of the CONTAINS operator

Now that you know the syntax of the CONTAINS operator, you can write queries that use it.

The following query, which displays all Item records whose Cat–description field contains the word “hockey,” demonstrates the CONTAINS operator in its simplest form:
FOR EACH item  
  WHERE cat-description CONTAINS "hockey": 
  DISPLAY item. 
END. 
The following is the equivalent query in SQL-89, which also allows CONTAINS:
SELECT * FROM item 
  WHERE cat-description CONTAINS "hockey". 
A CONTAINS string can contain multiple words connected by the AND operator (AND or &) and the OR operator (OR, |, or ^), optionally grouped by parentheses. For example:
...CONTAINS "free | gratis | (no & charge)"... 
Note: The AND operator takes precedence over the OR operator. To override this default, use parentheses. Using parentheses can also make the text of a query clearer.

A CONTAINS string containing multiple contiguous words, such as:
...CONTAINS "credit hold"... 
is equivalent to a CONTAINS string containing multiple words connected by AND, such as:
...CONTAINS "credit AND hold"... 
If a CONTAINS string contains multiple words, the order of the words is not significant. To retrieve records in a specific order, use the CONTAINS operator with the MATCHES operator. The following WHERE clause retrieves records whose comments field contains the words “credit” and “hold” in that order, perhaps with other words in between:
...WHERE comments CONTAINS "credit hold" 
    AND comments MATCHES "*credit*hold*"... 
Word indexes are case insensitive unless a field participating in the word index is case sensitive. The following two WHERE clauses are equivalent:
...WHERE comments CONTAINS "CREDIT HOLD"... 
...WHERE comments CONTAINS "credit hold"... 
You can combine CONTAINS with other search criteria, as in the following WHERE clause, which searches for records whose city field is Boston and whose comments field contains the word “credit” and either the word “hold” or “watch”:
...WHERE city = "Boston" 
    AND comments CONTAINS "credit (hold ^ watch)"... 
The following example demonstrates the use of a variable with the CONTAINS operator within the WHERE clause:
DEFINE VARIABLE search_wrd AS CHARACTER. 
ASSIGN search_wrd = "The". 
FOR EACH customer WHERE name CONTAINS search_wrd: 
    DISPLAY cust-num name. 
END. 